package flextv.data {

import flash.data.SQLConnection;
import flash.data.SQLStatement;
import flash.events.SQLErrorEvent;
import flash.events.SQLEvent;
import flash.filesystem.File;

import flextv.core.logic.Logger;
import flextv.core.vo.Playlist;
import flextv.core.vo.StoredItem;

import mx.collections.ArrayCollection;
import mx.logging.ILogger;

public class PersistenceServiceImpl implements IPersistenceService {
	
	private static const LOG: ILogger = Logger.getLog(PersistenceServiceImpl);

	internal static const TBL_PLAYLIST: String = "Playlist";
	internal static const TBL_STORED_ITEM: String = "StoredItem";

	// created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

	private static const SQL_CREATE_PLAYLIST: String =
		"CREATE TABLE IF NOT EXISTS " + TBL_PLAYLIST + " ( \
			id INT PRIMARY KEY AUTOINCREMENT, \
			title TEXT NOT NULL \
		)";
	
	
	// "REFERENCES x (id) ON DELETE CASCADE" does not work SQLite
	private static const SQL_CREATE_STORED_ITEM: String =
		"CREATE TABLE IF NOT EXISTS " + TBL_STORED_ITEM + " ( \
				yid           TEXT NOT NULL PRIMARY KEY, \
				title         TEXT NOT NULL, \
				downloaded    INT, \
				duration      INT , \
				fileUri       TEXT NOT NULL, \
				playlistId    INT, \
				FOREIGN KEY (playlistId) REFERENCES " + TBL_PLAYLIST + " (id) \
			)";
	
	private static const SQL_CREATE_TRIGGER_PLAYLIST_DELETE: String =
		"CREATE TRIGGER IF NOT EXISTS cascadePlaylistDelete \
			BEFORE DELETE ON " + TBL_PLAYLIST + " \
			FOR EACH ROW \
			BEGIN \
				DELETE FROM " + TBL_STORED_ITEM + " WHERE playlistId = old.id; \
			END";
	
	private static const SQL_INSERT_PLAYLIST: String = "INSERT INTO " + TBL_PLAYLIST + " (title) VALUES (:title)";
		
	private static const SQL_INSERT_STORED_ITEM: String = "INSERT INTO " + TBL_STORED_ITEM + " \
							(yid, title, downloaded, duration, fileUri, playlistId) VALUES \
							(:yid, :title, :downloaded, :duration, :fileUri, :playlistId)";
							
	private static const SQL_DELETE_PLAYLIST: String = "DELETE FROM " + TBL_PLAYLIST + " WHERE id=:id";
	
	private static const SQL_DELETE_STORED_ITEM: String = "DELETE FROM " + TBL_STORED_ITEM + " WHERE yid=:yid"; 
	
	
	private var _connection: SQLConnection;			
	
	private var _stmtFetchPlaylists: SQLStatement;
	private var _stmtFetchStoredItems: SQLStatement;
	
	private var _stmtInsertPlaylist: SQLStatement;
	private var _stmtInsertStoredItem: SQLStatement;
	
	private var _stmtDeletePlaylist: SQLStatement;
	private var _stmtDeleteStoredItem: SQLStatement;
	
	/** injected by spring */
	public var databaseFileName: String;
	
	public function PersistenceServiceImpl() {
	}
	
	internal function getConnection(): SQLConnection {
		return _connection;
	}
	
	public function open(): void {
		_connection = new SQLConnection();
		_connection.addEventListener(SQLErrorEvent.ERROR, function(event: SQLEvent): void {
			throw new Error("Could not connect to local Database!");
		});
		
		_connection.open(File.applicationStorageDirectory.resolvePath(databaseFileName));
		
		this.prepareStatement(SQL_CREATE_PLAYLIST, Playlist).execute();
		this.prepareStatement(SQL_CREATE_STORED_ITEM, StoredItem).execute();
		this.prepareStatement(SQL_CREATE_TRIGGER_PLAYLIST_DELETE).execute();
		
		
		_stmtFetchPlaylists = this.prepareStatement("SELECT * FROM " + TBL_PLAYLIST, Playlist);
		_stmtFetchStoredItems = this.prepareStatement("SELECT * FROM " + TBL_STORED_ITEM, StoredItem);
		
		_stmtInsertPlaylist = this.prepareStatement(SQL_INSERT_PLAYLIST);
		_stmtInsertStoredItem = this.prepareStatement(SQL_INSERT_STORED_ITEM);
		
		_stmtDeletePlaylist = this.prepareStatement(SQL_DELETE_PLAYLIST);
		_stmtDeleteStoredItem = this.prepareStatement(SQL_DELETE_STORED_ITEM);
	}
	
	private function prepareStatement(sql: String, itemClass: Class = null): SQLStatement {
		const stmt: SQLStatement = new SQLStatement();
		stmt.sqlConnection = _connection;
		stmt.text = sql;
		if(itemClass != null) {
			stmt.itemClass = itemClass;
		}
		return stmt;
	}
	
	
	/** from IPersistenceService */
	public function fetchPlaylists(playlistResponder: PlaylistResponder): ArrayCollection {
		LOG.info("fetchPlaylists()");
		
		_stmtFetchPlaylists.execute(-1, playlistResponder);
		const playlists: ArrayCollection = playlistResponder.getPlaylists();
		const storedItems: ArrayCollection = this.fetchStoredItems(new StoredItemResponder());
		
		// add items to proper playlist
		const playlistIdMap: Array = new Array();
		for each(var px: Playlist in playlists) {
			px.storedItems = new ArrayCollection();
			playlistIdMap[px.id] = px;
		}
		
		for each(var si: StoredItem in storedItems) {
			var p: Playlist = playlistIdMap[si.playlistId] as Playlist;
			p.storedItems.addItem(si);
		}
		
		return playlists;
	}
	
	private function fetchStoredItems(responder: StoredItemResponder): ArrayCollection {
		LOG.info("fetchStoredItems()");
		_stmtFetchStoredItems.execute(-1, responder);
		return responder.getStoredItems();
	}
	
	/** from IPersistenceService */
	public function insertPlaylist(playlist: Playlist): void {
		LOG.info("insertPlaylist(playlist="+playlist+")");
		
		_stmtInsertPlaylist.parameters[":title"] = playlist.title;
		_stmtInsertPlaylist.execute();
		
		playlist.id = _connection.lastInsertRowID;
		
		/* NO: inserting playlist inserts playlist only, without any stored item (cause user cant do otherwise)
		_connection.begin();
		
		var committed: Boolean = false;
		try {
			_stmtInsertPlaylist.parameters[":title"] = playlist.title;
			_stmtInsertPlaylist.execute();
			
			playlist.id = _connection.lastInsertRowID;
			
			for each(var item: StoredItem in playlist.storedItems) {
				this.insertStoredItem(item, playlist);
			}
			
			_connection.commit();
			committed = true;
		} finally {
			if(committed == false) {
				_connection.rollback();
			}
		}
		*/
	}
	
	/** from IPersistenceService */
	public function insertStoredItem(item: StoredItem, playlist: Playlist): void {
		LOG.info("insertStoredItem(item="+item+"; playlist="+playlist+")");
		
		if(item.playlistId != playlist.id) {
			// throw new Error("item.playlistId ["+item.playlistId+"] != playlist.id ["+playlist.id+"]");
			item.playlistId = playlist.id;
		}
		
		// "(@yid, @title, @downloaded, @watchId, @duration, @fileUri, @playlistTitle)"
		_stmtInsertStoredItem.parameters[":yid"] = item.yid;
		_stmtInsertStoredItem.parameters[":title"] = item.title;
		_stmtInsertStoredItem.parameters[":downloaded"] = 100;
		_stmtInsertStoredItem.parameters[":duration"] = item.duration;
		_stmtInsertStoredItem.parameters[":fileUri"] = item.fileUri;
		_stmtInsertStoredItem.parameters[":playlistId"] = item.playlistId;
		_stmtInsertStoredItem.execute();
	}
	
	/** from IPersistenceService */
	public function deletePlaylist(playlist: Playlist): void {
		LOG.info("deletePlaylist(playlist="+playlist+")");
		
		_stmtDeletePlaylist.parameters[":id"] = playlist.id;
		_stmtDeletePlaylist.execute();
	}
	
	/** from IPersistenceService */
	public function deleteStoredItem(item: StoredItem): void {
		LOG.info("deleteStoredItem(item="+item+")");
		
		_stmtDeleteStoredItem.parameters[":yid"] = item.yid;
		_stmtDeleteStoredItem.execute();
	}
	
	/** from IPersistenceService */
	public function close(): void {
		if(_connection == null) {
			LOG.warn("Could not close DB connection because: _connection == null");
			return;
		}
		
		try {
			_connection.close();
		} catch(e: Error) {
			LOG.warn("Could not close DB connection! " + e.message);
		} finally {
			_connection = null;
		}
	}
	
}
}